<?php
namespace app\api\controller;
use app\common\BaseController;
use think\Db;
use app\common\Logs;
use app\api\model\TeamOutlay as TeamOutlayModel;
use app\api\model\Single as SingleModel;

class Payable extends BaseController{    
    public function __construct(){
        parent::__construct();       
    }
    public function index(){  
        $twhere = []; 
        $where = [];
        $sub_seach=[];  
        $params = $this->request->param();       
       
        if(!empty($params)){           
            //按出发日期查询
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                $sub_seach["start_date"] = [['>=',$params['start_date']],['<=',$params['end_date']]];    
                $where['start_date']=[['>=',$params['start_date']],['<=',$params['end_date']]];   
            }else{
                return '缺少搜索日期参数';
            }           
            //按供应商查询
            if(!empty($params['supplier_id'])){               
                $twhere['supplier_id'] = $params['supplier_id'];
                $where['supplier_id'] = $params['supplier_id'];
            } 
            //模糊查询           
            if(!empty($params['keyword'])){     
                $sub_seach["title"] = ['like',"%{$params['keyword']}%"];           
               
                $where['product_name'] = ['like',"%{$params['keyword']}%"];            
            }
            if(!empty($params['client_id'])){               
                $where['client_id'] = $params['client_id'];             
            } 
             //按操作员查询
             if(!empty($params['operator_id'])){
                $sub_seach["operator_id"] = $params['operator_id'];
                $where['operator_id'] = $params['operator_id'];
            }  
            //按操作员查询
            if(!empty($params['sn'])){
                $sub_seach["sn"] = ['like',"%{$params['sn']}%"];  
                $where['sn'] = ['like',"%{$params['sn']}%"];             
            }              
        }        
        
        $teamOutlayModel = new TeamOutlayModel();
        $singleModel = new SingleModel();
        
        if(!empty($params['client_id'])){
            $a=[];
        }else{
            $a = $teamOutlayModel            
            ->hasWhere("team",$sub_seach)             
            ->with(["team"=>["operator"],"supplier","inInvoices","payments"])
            ->where($twhere)          
            ->select();  
        }
                
        $b = $singleModel->with(["supplier","client","inInvoices","payments","operator"])->where($where)->select();
        foreach ($b as &$v) {  
            $arr=[];            
            foreach(explode(',', $v['guest_id']) as $g){               
                $s=Db::name("guest")->field("id,guest_name")->where("id",$g)->find();
                array_push($arr,$s["guest_name"]);
            }          
            $v['guest']=implode('，',$arr);
        }
        $c = array_merge($a,$b);        

        $datalist = array(
            'datas' => $c,            
            'dataCount' => count($c)
        );
        return $datalist;    
    }   

    public function csv(){   
        $where = [];
        $where = [];
        $twhere=[];
        $swhere=[];
        $params = $this->request->param();       
        $is_zero = false;  
        $page = 1;
        $pageCount = 10;        
        
        if(!empty($params)){           
            //按出发日期查询
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                $twhere['b.start_date'] = [['>=',$params['start_date']],['<=',$params['end_date']]];
                $swhere['start_date'] = [['>=',$params['start_date']],['<=',$params['end_date']]];
            }else{
                if(!empty($params['start_date'])){
                    $twhere['b.start_date'] = ['>= ',$params['start_date']];   
                    $swhere['start_date'] = ['>= ',$params['start_date']];  
                }
                if(!empty($params['end_date'])){
                    $twhere['b.start_date'] = ['<=',$params['end_date']];  
                    $swhere['start_date'] = ['<=',$params['end_date']];  
                }
            }
            //页码            
            $page = !empty($params['page']) ? $params['page'] : $page;
            $pageCount = !empty($params['page_count']) ? $params['page_count'] : $pageCount;
            //按供应商查询
            if(!empty($params['supplier_id'])){
                $twhere['a.supplier_id'] = $params['supplier_id'];
                $swhere['supplier_id'] = $params['supplier_id'];
            } 
             //按操作员查询
             if(!empty($params['operator_id'])){
                $twhere['b.operator_id'] = $params['operator_id'];
                $swhere['operator_id'] = $params['operator_id'];
            }  
            //不显示欠款为0
            if(!empty($params['is_zero'])){
                $is_zero = $params['is_zero'];
            }     
        }        
        
        
        $a = Db::name("team_outlay")->alias('a')->field("a.id as outlay_id,b.id as pro_id,a.type,a.supplier_id,a.supplier_id as guest_id,a.supplier_id as client_id,a.product_name,a.remark,b.title,b.sn,b.start_date,b.end_date,a.num,a.cost_price,a.cost_price as pay_price,a.invoice_status,a.is_review,b.operator_id")->join("lt_team b","a.parent_id = b.id","LEFT")->where($twhere)->buildSql();
        $b = Db::name("single")->field("id as outlay_id,id as pro_id,type,supplier_id,guest_id,client_id,product_name,remark,product_name as title,sn,start_date,end_date,num,cost_price,pay_price,invoice_status,outlay_review as is_review,operator_id")->where($swhere)->buildSql();
        $datas = Db::table($a.' a')->union([$b])->order('start_date', 'desc')->select();
        foreach($datas as $key => &$val){       
            $money=0;     
            $total=0;       
            $val['is_review'] = (int)$val['is_review'];
            $rec = Db::name("payment")->where(["outlay_id"=>$val['outlay_id'],"type"=>$val['type']])->select();            
            $val['payments'] = $rec;   
            foreach($rec as $key2 =>$val2){
                $money += $val2['money'];
            }
           $total = $val['cost_price'] * $val['num'];
           if($is_zero=="true" && $total - $money == 0){
            array_splice($datas, $key, 1);
           }
        }        
       
        return $datas;     
    }   
     //审核
     public function review(){
        $params = json_decode(file_get_contents('php://input'),true);
        $count = 0;
        foreach ($params['list'] as $key => $item) {          
            if(in_array($item['type'], [1,2,3,10,16,17])){
                $teamOutlayModel = new TeamOutlayModel();
                $old =$teamOutlayModel->where('id',$item['id'])->find();  
                $data=$teamOutlayModel->where('id',$item['id'])->update(['is_review'=>'1']); 
                if($data){    
                    $data=$teamOutlayModel->where('id',$item['id'])->find();
                    $parent = $teamOutlayModel->alias('a')->join('lt_team b','a.parent_id=b.id')->where('a.id',$old['id'])->find();
                    Logs::add("审核支出记录",$item['operator_id'],$parent["sn"], json_encode($old,JSON_UNESCAPED_UNICODE),json_encode($data,JSON_UNESCAPED_UNICODE));       
                }
            }else{
                $singleModel = new SingleModel();
                $old =$singleModel->where('id',$item['id'])->find();  
                $data=$singleModel->where('id',$item['id'])->update(['outlay_review'=>'1']);  
                if($data){   
                    $data=$singleModel->where('id',$item['id'])->find();     
                    $parent = $singleModel->where('id',$item['id'])->find();
                    Logs::add("审核支出记录",$item['operator_id'],$parent["sn"], json_encode($old,JSON_UNESCAPED_UNICODE),json_encode($data,JSON_UNESCAPED_UNICODE));       
                }
            }
            if($data){
                $count++;
            }            
        }
        return $count;
    }   
    //取消审核
    public function cancel_review(){
        $params = json_decode(file_get_contents('php://input'),true);
        $count = 0;
        foreach ($params['list'] as $key => $item) {          
            if(in_array($item['type'], [1,2,3,10,16,17])){
                $teamOutlayModel = new TeamOutlayModel();
                $old =$teamOutlayModel->where('id',$item['id'])->find();  
                $data=$teamOutlayModel->where('id',$item['id'])->update(['is_review'=>'0']); 
                if($data){    
                    $data=$teamOutlayModel->where('id',$item['id'])->find();
                    $parent = $teamOutlayModel->alias('a')->join('lt_team b','a.parent_id=b.id')->where('a.id',$old['id'])->find();
                    Logs::add("解除审核支出记录",$item['operator_id'],$parent["sn"], json_encode($old,JSON_UNESCAPED_UNICODE),json_encode($data,JSON_UNESCAPED_UNICODE));       
                }
            }else{
                $singleModel = new SingleModel();
                $old =$singleModel->where('id',$item['id'])->find();  
                $data=$singleModel->where('id',$item['id'])->update(['outlay_review'=>'0']);  
                if($data){   
                    $data=$singleModel->where('id',$item['id'])->find();     
                    $parent = $singleModel->where('id',$item['id'])->find();
                    Logs::add("解除审核支出记录",$item['operator_id'],$parent["sn"], json_encode($old,JSON_UNESCAPED_UNICODE),json_encode($data,JSON_UNESCAPED_UNICODE));       
                }
            }
            if($data){
                $count++;
            }            
        }
        return $count;
    }

    
    //导出excel
    public function export_excel(){     
        $title = array('供应商','团号','线路/订单内容','出发日期','回程日期','数量','单价','金额','已收款','尚欠','状态');
        include(EXTEND_PATH.'phpexcel/PHPExcel.php'); 
        $obj = new \PHPExcel();      
        $obj->getActiveSheet()->getColumnDimension('A')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('B')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('C')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('F')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('G')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('I')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('N')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('O')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('P')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('Q')->setWidth(18);
        $obj->getActiveSheet()->getColumnDimension('R')->setWidth(18);
        //横向单元格标识      
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');  
        $obj->getActiveSheet(0)->setTitle('sheet名称');   //设置sheet名称      
        $_row = 1;   //设置纵向单元格标识      
        if($title){      
            $_cnt = count($title);    
            $i = 0;      
            foreach($title AS $v){   //设置列标题      
                $obj->setActiveSheetIndex(0)->setCellValue($cellName[$i].$_row, $v);      
                $i++;      
            }      
            $_row++;      
        }      
        $where = [];
        $params = $this->request->param();
        if(!empty($params)){           
            //按出发日期查询
            if(!empty($params['start_date']) && !empty($params['end_date'])){
                $where['start_date'] = [['>= time',$params['start_date']],['<= time',$params['end_date']]];
            }else{
                if(!empty($params['start_date'])){
                    $where['start_date'] = ['>= time',$params['start_date']];  
                }
                if(!empty($params['end_date'])){
                    $where['start_date'] = ['<= time',$params['end_date']];  
                }
            }
           
            //按供应商查询
            if(!empty($params['supplier_id'])){
                $where['supplier_id'] = $params['supplier_id'];
            } 
            //不显示欠款为0
            if(!empty($params['is_zero'])){
                $is_zero = $params['is_zero'];
            }     
        }        
        
        $a = Db::name("team_outlay")->alias('a')->field("a.id as outlay_id,b.id as pro_id,a.type,a.supplier_id,b.title,b.sn,b.start_date,b.end_date,a.num,a.cost_price,a.invoice_status,a.is_review")->join("lt_team b","a.parent_id = b.id","LEFT")->where($where)->buildSql();
        $b = Db::name("single")->field("id as outlay_id,id as pro_id,type,supplier_id,product_name as title,sn,start_date,end_date,num,cost_price,invoice_status,outlay_review as is_review")->where($where)->buildSql();
        $datas = Db::table($a.' a')->union([$b])->order('start_date', 'desc')->select();
        foreach($datas as $key => &$val){       
            $money=0;     
            $total=0;       
            $rec = Db::name("payment")->where("outlay_id",$val['outlay_id'])->select();            
            $val['payments'] = $rec;   
            foreach($rec as $key2 =>$val2){
                $money += $val2['money'];
            }
           $total = $val['cost_price'] * $val['num'];
           if($is_zero=="true" && $total - $money == 0){
            array_splice($datas, $key, 1);
           }
        }
       
        //填写数据      
        if($datas){      
            $i = 0;      
            foreach($datas AS $_v){                
                $obj->getActiveSheet(0)->setCellValue($cellName[0] . ($i+$_row), $_v['supplier_id']);     
                $obj->getActiveSheet(0)->setCellValue($cellName[1] . ($i+$_row), $_v['sn']);
                $obj->getActiveSheet(0)->setCellValueExplicit($cellName[2] . ($i+$_row), $_v['title'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $obj->getActiveSheet(0)->setCellValueExplicit($cellName[3] . ($i+$_row), $_v['start_date'],\PHPExcel_Cell_DataType::TYPE_STRING);
                $obj->getActiveSheet(0)->setCellValueExplicit($cellName[4] . ($i+$_row), $_v['end_date'],\PHPExcel_Cell_DataType::TYPE_STRING);    
                $obj->getActiveSheet(0)->setCellValue($cellName[5] . ($i+$_row), $_v['num']);
                $obj->getActiveSheet(0)->setCellValue($cellName[6] . ($i+$_row), $_v['cost_price']);
                $obj->getActiveSheet(0)->setCellValue($cellName[7] . ($i+$_row), $_v['cost_price']*$_v['num']);
                $money=0;
                foreach ($_v['payments'] as $key => $item) {                    
                    $money += $item['money'];                    
                }
               
                $obj->getActiveSheet(0)->setCellValue($cellName[8] . ($i+$_row), $money);    
                $obj->getActiveSheet(0)->setCellValue($cellName[9] . ($i+$_row), $_v['cost_price'] * $_v['num'] - $money);    
                $obj->getActiveSheet(0)->setCellValue($cellName[10] . ($i+$_row), $_v['is_review']);                
                $i++;      
            }      
        }  
        //文件名处理      
        
        $fileName = uniqid(time(),true);      
        
        $objWrite = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');  
        $isDown=true;
        if($isDown){   //网页下载      
            header('pragma:public');      
            header("Content-Disposition:attachment;filename=$fileName.xls");      
            $objWrite->save('php://output');exit;      
        }      
        $_fileName = iconv("utf-8", "gb2312", $fileName);   //转码      
        $_savePath = $savePath.$_fileName.'.xlsx';      
         $objWrite->save($_savePath);  
         return $savePath.$fileName.'.xlsx';      
    }     
}
